package com.pb.infra.dynamicSetting.dynamTableColumnCfg.service;

import cn.hutool.core.util.IdUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.pb.infra.dynamicSetting.dynamTableCfg.entity.MaFrDynamTableCfg;
import com.pb.infra.dynamicSetting.dynamTableCfg.mapper.MaFrDynamTableCfgMapper;
import com.pb.infra.dynamicSetting.dynamTableColumnCfg.entity.MaFrDynamTableColumnCfg;
import com.pb.infra.dynamicSetting.dynamTableColumnCfg.mapper.MaFrDynamTableColumnCfgMapper;
import com.pb.infra.dynamicSetting.dynamTableColumnCfg.runnable.DynTabColDataRunnable;
import com.pb.infra.dynamicSetting.dynamicSettingTable.entity.DictVO;
import com.pb.infra.oth.entity.common.Result;
import com.pb.infra.oth.util.*;
import com.pb.infra.param.mapper.ImportConfigMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ThreadFactory;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * 动态表格 列管理(MaFrTableColumn)表服务实现类
 *
 * @author makejava
 * @since 2022-03-09 15:12:07
 */
@Slf4j
@Service("maFrTableColumnService")
public class MaFrTableColumnService {

    private final static String[] COLNAMES = {"序号", "列-中文名", "列-英文名（表字段）", "列-组件类型（默认：input）", "查询（TRUE:开启，FALSE:关闭）", "模糊（TRUE:开启，FALSE:关闭）", "是否隐藏（TRUE:是，FALSE:否）"};


    @Value("${file.path}")
    private String path;

    /**
     * 线程
     */
    final static int PAGE_SIZE = 1000;

    @Resource
    private MaFrDynamTableColumnCfgMapper maFrDynamTableColumnCfgMapper;

    @Resource
    private MaFrDynamTableCfgMapper maFrDynamTableCfgMapper;

    @Autowired
    private ImportConfigMapper importConfigMapper;


    @Resource
    private CDRCBFileEncryptionRest cdrcbFileEncryptionRest;

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    public Result queryById(String id) {
        return Result.ok(this.maFrDynamTableColumnCfgMapper.queryById(id));
    }

    /**
     * 分页查询
     *
     * @param maFrDynamTableColumnCfg 筛选条件
     * @return 查询结果
     */
    public Result queryList(MaFrDynamTableColumnCfg maFrDynamTableColumnCfg) {
        if (maFrDynamTableColumnCfg != null && maFrDynamTableColumnCfg.getPageIndex() != null && maFrDynamTableColumnCfg.getPageSize() != null) {
            PageHelper.startPage(maFrDynamTableColumnCfg.getPageIndex(), maFrDynamTableColumnCfg.getPageSize());
        }
        List<MaFrDynamTableColumnCfg> maFtpReckonHpList = this.maFrDynamTableColumnCfgMapper.queryList(maFrDynamTableColumnCfg);
        maFtpReckonHpList.forEach(item -> {
            if (!StringUtil.isEmpty(item.getPropsJson())) {
                DictVO dVO = JSON.parseObject(item.getPropsJson(), DictVO.class);
                item.setDictLabel(dVO.getLabel());
                item.setValue(dVO.getValue());
                item.setProps(dVO);
            }

        });
        return Result.ok(new PageInfo(maFtpReckonHpList).getTotal(), maFtpReckonHpList);
    }

    /**
     * 新增数据
     *
     * @param maFrDynamTableColumnCfg 实例对象
     * @return 实例对象
     */
    public Result insert(MaFrDynamTableColumnCfg maFrDynamTableColumnCfg) {
        if (!StringUtil.isEmpty(maFrDynamTableColumnCfg.getProp())) {
            maFrDynamTableColumnCfg.setProp(maFrDynamTableColumnCfg.getProp().toUpperCase(Locale.ENGLISH));
        }
        DictVO dVO = new DictVO();
        dVO.setLabel(maFrDynamTableColumnCfg.getDictLabel());
        dVO.setValue(maFrDynamTableColumnCfg.getValue());
        maFrDynamTableColumnCfg.setProps(dVO);
        maFrDynamTableColumnCfg.setPropsJson(JSON.toJSONString(maFrDynamTableColumnCfg.getProps()));
        maFrDynamTableColumnCfg.setSearch(maFrDynamTableColumnCfg.getSearch() == null ? false : maFrDynamTableColumnCfg.getSearch());
        maFrDynamTableColumnCfg.setIsLike(maFrDynamTableColumnCfg.getIsLike() == null ? false : maFrDynamTableColumnCfg.getIsLike());
        return Result.ok(this.maFrDynamTableColumnCfgMapper.insert(maFrDynamTableColumnCfg));
    }

    /**
     * 修改数据
     *
     * @param maFrDynamTableColumnCfg 实例对象
     * @return 实例对象
     */
    public Result update(MaFrDynamTableColumnCfg maFrDynamTableColumnCfg) {
        if (!StringUtil.isEmpty(maFrDynamTableColumnCfg.getProp())) {
            maFrDynamTableColumnCfg.setProp(maFrDynamTableColumnCfg.getProp().toUpperCase(Locale.ENGLISH));
        }
        DictVO dVO = new DictVO();
        dVO.setLabel(maFrDynamTableColumnCfg.getDictLabel());
        dVO.setValue(maFrDynamTableColumnCfg.getValue());
        maFrDynamTableColumnCfg.setProps(dVO);
        maFrDynamTableColumnCfg.setPropsJson(JSON.toJSONString(maFrDynamTableColumnCfg.getProps()));
        maFrDynamTableColumnCfg.setSearch(maFrDynamTableColumnCfg.getSearch() == null ? false : maFrDynamTableColumnCfg.getSearch());
        maFrDynamTableColumnCfg.setIsLike(maFrDynamTableColumnCfg.getIsLike() == null ? false : maFrDynamTableColumnCfg.getIsLike());
        return Result.ok(this.maFrDynamTableColumnCfgMapper.update(maFrDynamTableColumnCfg));
    }

    /**
     * 通过主键删除数据
     *
     * @param maFrDynamTableColumnCfg 主键
     * @return 是否成功
     */
    public Result delete(MaFrDynamTableColumnCfg maFrDynamTableColumnCfg) {
        return Result.ok(this.maFrDynamTableColumnCfgMapper.delete(maFrDynamTableColumnCfg) > 0);
    }

    /**
     * 同步表字段
     *
     * @param maFrDynamTableColumnCfg 表格参数
     * @return 执行结果
     */
    @Transactional
    public Result syncTableCol(MaFrDynamTableColumnCfg maFrDynamTableColumnCfg) {
        MaFrDynamTableCfg dynamTableCfg = new MaFrDynamTableCfg();
        dynamTableCfg.setId(maFrDynamTableColumnCfg.getTableId());

        maFrDynamTableCfgMapper.queryList(dynamTableCfg).forEach(item -> {
            Map<String, Object> map = new HashMap<>();
            map.put("dbUser", item.getDestDataSchemaName());
            map.put("tableName", item.getTargetTableName());
            map.put("tableId", maFrDynamTableColumnCfg.getTableId());

            maFrDynamTableCfgMapper.dbTableCol(map).forEach(colItem -> {
                MaFrDynamTableColumnCfg col = new MaFrDynamTableColumnCfg();
                Integer id = Integer.parseInt(maFrDynamTableColumnCfgMapper.maxId(maFrDynamTableColumnCfg.getTableId())) + 1;
                col.setId(id.toString());
                col.setTableId(maFrDynamTableColumnCfg.getTableId());
                col.setLabel(colItem.get("label"));
                col.setProp(colItem.get("prop"));
                col.setType("input");
                DictVO dVO = new DictVO();
                dVO.setLabel("");
                dVO.setValue("");
                col.setProps(dVO);
                col.setPropsJson("");
                col.setSearch(false);
                col.setIsLike(false);
                col.setHide(false);
                col.setSearchLabelWidth("80");
                maFrDynamTableColumnCfgMapper.insert(col);
            });

        });

        return Result.ok("执行成功");
    }


    /**
     * 首次校验
     * 主要判断内容是否存在
     *
     * @param data 当前列数据
     * @param row  行号
     * @param col  列号
     * @return StringBuilder
     */
    private StringBuilder dataValidate(String data, int row, int col) {

        StringBuilder rowErrInfo = new StringBuilder();

        switch (col) {
            case 0:
                //序号
                Pattern pattern = Pattern.compile("^[1-9]{1,}\\d*$|^0$");
                if (!pattern.matcher(data).matches()) {
                    rowErrInfo.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第").append(col).append("列 第<").append(row).append(">行 不是整型数字（1,2,3...）");

                }

                break;
            case 1:
                //列-中文名

                if (StringUtil.isEmpty(data)) {
                    rowErrInfo.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第").append(col).append("列 第<").append(row).append(">行 列-中文名不存在");
                }
                break;
            case 2:
                //列-英文名（表字段）
                if (StringUtil.isEmpty(data)) {
                    rowErrInfo.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第").append(col).append("列 第<").append(row).append(">行 列-英文名（表字段）不存在");
                }
                break;
            case 3:
                //列-组件类型（默认：input）
                if (StringUtil.isEmpty(data)) {
                    rowErrInfo.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第").append(col).append("列 第<").append(row).append(">行 列-组件类型 不存在");
                }
                break;
            case 4:
                //查询（TRUE:开启，FALSE:关闭）

                if (StringUtil.isEmpty(data)) {
                    rowErrInfo.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第").append(col).append("列 第<").append(row).append(">行 查询（TRUE:开启，FALSE:关闭）不存在");
                } else {

                    if (!StringUtil.isBoolean(data)) {
                        rowErrInfo.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第").append(col).append("列 第<").append(row).append(">行 查询（TRUE:开启，FALSE:关闭）必须是，(TRUE/FALSE)");
                    }
                }

                break;

            case 5:
                //模糊（TRUE:开启，FALSE:关闭）
                if (StringUtil.isEmpty(data)) {
                    rowErrInfo.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第").append(col).append("列 第<").append(row).append(">行 模糊（TRUE:开启，FALSE:关闭）不存在");
                } else {
                    if (!StringUtil.isBoolean(data)) {
                        rowErrInfo.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第").append(col).append("列 第<").append(row).append(">行 模糊（TRUE:开启，FALSE:关闭）必须是，(TRUE/FALSE)");
                    }

                }
                break;
            case 6:
                //是否隐藏（TRUE:是，FALSE:否）
                if (StringUtil.isEmpty(data)) {
                    rowErrInfo.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第").append(col).append("列 第<").append(row).append(">行 是否隐藏（TRUE:是，FALSE:否）不存在");
                } else {
                    if (!StringUtil.isBoolean(data)) {
                        rowErrInfo.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第").append(col).append("列 第<").append(row).append(">行 是否隐藏（TRUE:是，FALSE:否）必须是，(TRUE/FALSE)");
                    }
                }
                break;
        }


        return rowErrInfo;
    }

    /**
     * 导入数据格式转换
     *
     * @param dataList excel解析数据
     * @return 转换后结果
     */
    private List<MaFrDynamTableColumnCfg> dataFormat(List<String[]> dataList,String tableId) {
        List<MaFrDynamTableColumnCfg> list = new ArrayList<>();
        for (String[] item :
                dataList) {
            MaFrDynamTableColumnCfg columnCfg = new MaFrDynamTableColumnCfg();
            columnCfg.setTableId(tableId);

            DictVO dVO = new DictVO();
            dVO.setLabel("");
            dVO.setValue("");
            columnCfg.setProps(dVO);
            columnCfg.setPropsJson("");
            for (int i = 0; i < item.length; i++) {
                switch (i) {
                    case 0:
                        columnCfg.setId(item[i]);
                        break;
                    case 1:
                        columnCfg.setLabel(item[i]);
                        break;
                    case 2:
                        columnCfg.setProp(item[i]);
                        break;
                    case 3:
                        columnCfg.setType(item[i]);
                        break;
                    case 4:
                        columnCfg.setSearch(Boolean.parseBoolean(item[i]));
                        break;
                    case 5:
                        columnCfg.setIsLike(Boolean.parseBoolean(item[i]));
                        break;
                    case 6:
                        columnCfg.setHide(Boolean.parseBoolean(item[i]));
                        break;


                }


            }

            list.add(columnCfg);
        }

        return list;
    }
    private Object colDataCheck(Map<String, Object> paramMap, XSSFWorkbook workbook, int startRowNum, int sheetCount) {

        List<String[]> importDataList = new ArrayList<>();   //导入list数据集合
        boolean hasErr = false;                              //存在错误标志位
        StringBuilder errInfo = new StringBuilder();         //全局错误信息
        StringBuilder rowErrInfo;                            //行错误信息
        StringBuilder valErrInfo;                            //单元格错误信息

        HashSet<String> primaryKeySet = new HashSet<>();     //主键集（辅助检查主键是否重复）
        String primeKey;                                     //每行主键值
        DataFormatter formatter = new DataFormatter();
        ZipSecureFile.setMinInflateRatio(-1.0d);
        errInfo.append("<br/>");
        errInfo.append("**********   <span style='color:red;font-weight:bold;'>导入结果：失败</span>   **********");
        if (workbook.getNumberOfSheets() < sheetCount) {
            //如果导入文件工作簿数不足，则记录错误信息，返回
            errInfo.append("<br/><br/><br/>工作簿不足，导入文件应具备" + sheetCount + "个工作簿，请使用模板导入");
            return errInfo.toString();
        }
        //遍历工作薄
        errInfo.append("<br/><br/><br/>*** 开始检查工作簿一 ***");
        XSSFSheet sheet = workbook.getSheetAt(0);
        int totalCellCount = COLNAMES.length;
        String[] colNames = COLNAMES;


        for (int i = startRowNum; i <= sheet.getLastRowNum(); i++) {
            //遍历每一行（从工作簿第startDataRow行开始读取）
            XSSFRow row = sheet.getRow(i);
            if (row != null && !ExcelUtil.concatString(row, totalCellCount, formatter)) {
                //行数据错误标志位
                boolean rowErr = false;
                rowErrInfo = new StringBuilder();
                String[] importData = new String[totalCellCount];    //利率基本信息数据
                rowErrInfo.append("<br/><br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第 ").append(i + 1).append(" 行：");

                //便利每列数据
                for (int j = 0; j < totalCellCount; j++) {
                    //遍历每一个单元格
                    XSSFCell cell = row.getCell(j);

                    //校验数据
                    valErrInfo = this.dataValidate(ExcelUtil.getStringCellValues(formatter, cell), i, j);
                    if (valErrInfo.length() != 0) {
                        rowErrInfo.append(valErrInfo);
                        rowErr = true;
                        hasErr = true;
                    } else {
                        //每行数据
                        importData[j] = ExcelUtil.getStringCellValues(formatter, cell);

                    }

                }


                importDataList.add(importData);

                if (rowErr) {
                    //将行错误信息记录到全局错误信息中
                    errInfo.append(rowErrInfo.toString());
                }


            }
        }
        //返回最终结果
        if (hasErr) {
            return errInfo.toString();
        } else {
            StringBuilder rightInfo = new StringBuilder();
            rightInfo.append("<br/>");
            rightInfo.append("**********   <span style='color:green;font-weight:bold;'>导入结果：成功</span>   **********");
            rightInfo.append("<br/><br/>共计导入 <span style='color:blue;font-weight:bold;'>" + importDataList.size() + "</span> 条 维度认定数据");
            Map<String, Object> mapList = new HashMap<>();
            mapList.put("dataList", this.dataFormat(importDataList,paramMap.get("tableId").toString()) );
            mapList.put("rightInfo", rightInfo.toString());
            mapList.put("rateTerm", colNames);
            return mapList;
        }
    }


    @Transactional
    public void importAdd(List<MaFrDynamTableColumnCfg> importData,String tableId) {
        long startTimes = System.currentTimeMillis();


        maFrDynamTableColumnCfgMapper.delTableCol(tableId);

        //获取系统时间
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");

        List<List<MaFrDynamTableColumnCfg>> listMaps = new ArrayList<>();
        int page = 0;

        if (importData.size() % PAGE_SIZE == 0) {
            page = importData.size() / PAGE_SIZE;
        } else {
            page = importData.size() / PAGE_SIZE + 1;
        }
        //page等于开启的线程数
        for (int i = 0; i < page; i++) {
            listMaps.add(importData.stream().skip(PAGE_SIZE * i).limit(PAGE_SIZE).collect(Collectors.toList()));
        }


        ThreadFactory thread = new UserThreadFactory("动态表格-列配置" + sdf.format(new Date()));

        //每隔30条插入一次 分段执行

        final CountDownLatch latch = new CountDownLatch(listMaps.size());
        for (List<MaFrDynamTableColumnCfg> columnCfgList :
                listMaps) {

            thread.newThread(new DynTabColDataRunnable(latch, columnCfgList)).start();

        }
        try {
            latch.await();
            long endTimes = System.currentTimeMillis();
            System.err.println("所有线程执行完毕:" + (endTimes - startTimes) / 1000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }

    }

    public Result colImport(Map<String, Object> paramMap, MultipartHttpServletRequest multRequest) {
        InputStream input = null;
        try {
            MultipartFile excelFile = multRequest.getFile("excel-file");

            input = cdrcbFileEncryptionRest.fileDecryption(excelFile.getInputStream());
            XSSFWorkbook workbook = new XSSFWorkbook(input);
            Object checkMap = this.colDataCheck(paramMap, workbook, 1, 1);
            if (checkMap instanceof String) {
                //检查未通过
                return Result.error("-1", checkMap.toString());
            } else {
                //检查通过
                Map checkList = (Map) checkMap;
                List<MaFrDynamTableColumnCfg> dataList = (List<MaFrDynamTableColumnCfg>) checkList.get("dataList");
                importAdd(dataList,paramMap.get("tableId").toString());
                String rightInfo = checkList.get("rightInfo").toString();

                return Result.ok(rightInfo);
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return Result.error("-1", "导入失败");
        } finally {

            if (input != null) {
                try {
                    input.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    public void colExport(MaFrDynamTableColumnCfg maFrDynamTableColumnCfg, HttpServletResponse response) {

        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(ExportExcelUtil.headerStyle(), ExportExcelUtil.mainStyle());
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("动态表格-列配置", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 这里需要设置不关闭流


            String fileNames = IdUtil.randomUUID() + ".xlsx";
            ExcelWriter excelWriter = EasyExcel.write(fileNames).registerWriteHandler(horizontalCellStyleStrategy).build();

            WriteSheet writeSheet = EasyExcel.writerSheet(0, "动态表格-列配置").head(MaFrDynamTableColumnCfg.class).build();
            excelWriter.write(maFrDynamTableColumnCfgMapper.queryList(maFrDynamTableColumnCfg), writeSheet);

            cdrcbFileEncryptionRest.fileEncryption(excelWriter, response, fileNames);
        } catch (IOException e) {
            e.printStackTrace();
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");

        }

    }
}
